package com.ycw.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ycw.dao.HouseRepairDao;
import com.ycw.dao.TenantDao;
import com.ycw.util.DBUtil;

public class HouseRepairDaoImpl implements HouseRepairDao, TenantDao {
	private static DBUtil db = new DBUtil();
	private static HouseRepairDaoImpl hrdi;
	private Connection conn;

	private PreparedStatement ps;
	private ResultSet rs;

	private String sqlCount;
	private List<Object> list;

	@Override
	public Map<String, Object> queryAllHouseRepairData(int pageNum) {
		String sql = "select hr.*,h.h_region,h.h_village,h.h_unit,h.h_roomnum,l.l_name,l.l_phone from house h RIGHT join houserepair hr on h.h_id=hr.h_id left join logistics l on hr.l_id=l.l_id limit ?,10";
		pageNum = (pageNum - 1) * 10;
		Map<String, Object> resMap = new HashMap<>();
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, pageNum);
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();// 获取列的数量
			while (rs.next()) {
				Map<String, Object> map = new HashMap<>();
				for (int i = 1; i <= count; i++) {
					String key = rsmd.getColumnLabel(i);
					map.put(key, rs.getObject(key));
				}
				resMap.put(rs.getString("hr_id"), map);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		return resMap;
	}

	@Override
	public int queryAllHouseRepairData() {
		String sql = "select count(*) from house h RIGHT join houserepair hr on h.h_id=hr.h_id left join logistics l on hr.l_id=l.l_id";
		int Count = 0;
		try {
			try {
				conn = db.getConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				Count = rs.getInt("count(*)");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			db.closeConn();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return Count;
	}

	@Override
	public Map<String, Object> queryAllHouseRepairDataByStateOrTime(String hr_state, String hr_time, int pageNum) {
		String sql = "select hr.*,h.h_region,h.h_village,h.h_unit,h.h_roomnum,l.l_name,l.l_phone from house h RIGHT join houserepair hr on h.h_id=hr.h_id left join logistics l on hr.l_id=l.l_id where 1=1";
		String sqlCount = "select count(*) from house h RIGHT join houserepair hr on h.h_id=hr.h_id left join logistics l on hr.l_id=l.l_id where 1=1";

		pageNum = (pageNum - 1) * 10;
		StringBuffer sb = new StringBuffer(sql);
		StringBuffer sbCount = new StringBuffer(sqlCount);

		List<Object> list = new ArrayList<Object>();
		List<Object> listCount = new ArrayList<Object>();
		if (hr_state != "") {
			sb.append(" and hr_state=?");
			sbCount.append(" and hr_state=?");
			list.add(hr_state);
			listCount.add(hr_state);
		}
		if (hr_time != "") {
			sb.append(" and hr_time=?");
			sbCount.append(" and hr_time=?");
			list.add(hr_time);
			listCount.add(hr_time);
		}
		this.sqlCount = sbCount.toString();
		this.list = listCount;
		sb.append(" limit ?,10");
		list.add(pageNum);
		sql = sb.toString();
		System.out.println(sql);
		Map<String, Object> resMap = new HashMap<>();
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<>();
				for (int i = 1; i <= count; i++) {
					String key = rsmd.getColumnLabel(i);
					map.put(key, rs.getObject(key));
				}

				resMap.put(rs.getString("hr_id"), map);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		return resMap;
	}

	@Override
	public int queryAllHouseRepairDataByStateOrTime() {
		int Count = 0;
		try {
			try {
				conn = db.getConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			ps = conn.prepareStatement(sqlCount);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				Count = rs.getInt("count(*)");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		return Count;
	}

	@Override
	public Map<String, Object> queryHouseRepairDataByID(int hr_id) {
		String sql = "select hr.*,h.h_region,h.h_village,h.h_unit,h.h_roomnum,l.l_name,l.l_phone from house h RIGHT join houserepair hr on h.h_id=hr.h_id left join logistics l on hr.l_id=l.l_id where hr_id=?";
		Map<String, Object> map = new HashMap<>();
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, hr_id);
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();// 获取列的数量
			while (rs.next()) {
				for (int i = 1; i <= count; i++) {
					String key = rsmd.getColumnLabel(i);
					map.put(key, rs.getObject(key));
				}

			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		return map;
	}

	@Override
	public boolean addHouseRepairData(List<Object> list) {

		String sql = "insert into houserepair(h_id,l_id,hr_time,hr_state,hr_explain) values(?,?,?,?,?)";
		int i = 0;
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (int) list.get(0));
			ps.setInt(2, (int) list.get(1));
			ps.setString(3, (String) list.get(2));
			ps.setString(4, (String) list.get(3));
			ps.setString(5, (String) list.get(4));
			i = ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (i > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 根据输入的房屋信息查询房屋h_id
	 * 
	 * @param list
	 * @return
	 */
	@Override
	public int queryH_idByAddData(String h_region, String h_village, String h_unit, String h_roomnum) {
		String sql = "select h_id from house where h_region=? and h_village=? and h_unit=? and h_roomnum=?";
		int h_id = 0;
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, h_region);
			ps.setString(2, h_village);
			ps.setString(3, h_unit);
			ps.setString(4, h_roomnum);
			rs = ps.executeQuery();
			if (rs.next()) {

				h_id = rs.getInt(1);

			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return h_id;
	}

	/**
	 * 根据输入的工号查询l_id
	 * 
	 * @param l_jobnumber
	 * @return
	 */
	@Override
	public int queryl_idByAddData(String l_name) {
		String sql = "select l_id from logistics where l_name=?";
		int l_id = 0;
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, l_name);
			rs = ps.executeQuery();
			if (rs.next()) {
				l_id = rs.getInt(1);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return l_id;

	}

	@Override
	public boolean deleteHouseRepairDataById(int hr_id) {
		String sql = "delete from houserepair where hr_id=?";
		int i = 0;
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, hr_id);
			i = ps.executeUpdate();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (i > 0) {
			return true;
		}
		return false;
	}

	@Override
	public boolean alterHouseRepairById(List<Object> list) {

		String sql = "update houserepair set h_id=?,l_id=?,hr_time=?,hr_state=?,hr_explain=? where hr_id=?";
		int i = 0;
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (int) list.get(0));
			ps.setInt(2, (int) list.get(1));
			ps.setString(3, (String) list.get(2));
			ps.setString(4, (String) list.get(3));
			ps.setString(5, (String) list.get(4));
			ps.setInt(6, (int) list.get(5));
			i = ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (i > 0) {
			return true;
		}
		return false;
	}

	private HouseRepairDaoImpl() {
	}

	public static HouseRepairDaoImpl getInstance() {
		if (hrdi == null) {
			hrdi = new HouseRepairDaoImpl();
		}
		return hrdi;
	}

	@Override
	public boolean queryDataByHidAndLid(int h_id, int l_id) {
		String sql = "select * from houserepair where h_id=? and l_id=?";
		boolean flage = false;
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, h_id);
			ps.setInt(2, l_id);

			rs = ps.executeQuery();
			if (rs.next()) {
				flage = true;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flage;
	}

	@Override
	public boolean tenantAddByString(String[] getAllTenant, int h_id) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean tenantDeleteById(int t_id) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public Map<String, Object> tenantDetailById(int t_id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean tenantUpdateByStringServlet(String[] updateOne, int t_id, String[] updateAddress) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public Map<String, Object> tenantCheckByName(String t_name, String t_phone, String t_time, int page) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Map<String, Object> tenantGetAll(int page) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int tenantAddByAddressGetId(String h_region, String h_village, String h_unit, String h_roomnum) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public List<String> tenantRegion() {
		List<String> list = new ArrayList<>();
		try {
			conn = db.getConn();
			String sql = " select DISTINCT h_region from house";
			ps = conn.prepareStatement(sql);
			// ps.setString(1, h_region);
			rs = ps.executeQuery();
			while (rs.next()) {
				list.add(rs.getString("h_region"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public List<String> tenantVillage(String h_region) {
		List<String> list = new ArrayList<>();
		try {
			conn = db.getConn();
			String sql = " select DISTINCT h_village from house where h_region=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, h_region);
			rs = ps.executeQuery();
			while (rs.next()) {
				list.add(rs.getString("h_village"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public List<String> tenantUnit(String h_region, String h_village) {
		List<String> list = new ArrayList<>();
		try {
			conn = db.getConn();
			String sql = " select DISTINCT h_unit from house where h_region=? and h_village =?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, h_region);
			ps.setString(2, h_village);
			rs = ps.executeQuery();
			while (rs.next()) {
				list.add(rs.getString("h_unit"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public List<String> tenantRoomnum(String h_region, String h_village, String h_unit) {
		List<String> list = new ArrayList<>();
		try {
			conn = db.getConn();
			String sql = " select DISTINCT h_roomnum from house where h_region=? and h_village =? and h_unit=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, h_region);
			ps.setString(2, h_village);
			ps.setString(3, h_unit);
			rs = ps.executeQuery();
			while (rs.next()) {
				list.add(rs.getString("h_roomnum"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public void tenantAdd() {
		// TODO Auto-generated method stub

	}

	@Override
	public boolean tenantAddByString(String[] getAllTenant, String[] getAllHouse) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public void tenantDelete() {
		// TODO Auto-generated method stub

	}

	@Override
	public void tenantUpdate() {
		// TODO Auto-generated method stub

	}

	@Override
	public void tenantCheck() {
		// TODO Auto-generated method stub

	}

	@Override
	public boolean queryDataByHidAndLid(int h_id, int l_id, int old_hid, int old_lid) {
		String sql = "select * from houserepair where h_id=? and l_id=? and not h_id=? and not l_id=?";
		boolean flage = false;
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, h_id);
			ps.setInt(2, l_id);
			ps.setInt(3, old_hid);
			ps.setInt(4, old_lid);

			rs = ps.executeQuery();
			if (rs.next()) {
				flage = true;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flage;
	}

}
